package com.pignest.framework.dao;

import java.math.BigDecimal;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * 通用jdbc dao ,实现常用的jdbc查询方法 <p/> History: create 2008-5-8 16:23:25
 * 
 * @version 0.1 2008-5-8 16:23:25
 */
@SuppressWarnings("unchecked")
public class BaseJdbcDao {
	protected final Log log = LogFactory.getLog(getClass());
	protected JdbcTemplate jdbcTemplate;
	protected String sPageSqlBefore = "" +
			"SELECT * FROM\n" + 
			"     (\n" + 
			"     SELECT A.*, rownum r\n" + 
			"     FROM\n"
			+ "          (";
	protected String sPageSqlAfter = "" +
			"          ) A\n" + 
			"     WHERE rownum <= ?\n" + 
			"     ) B\n" + "WHERE r > ?";

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public Long getTableSize(String sTableName) {
		String sql = "select count(*) as tablesize from {0} t";
		sql = genWholeSql(sql, new String[] { sTableName });
		log.info("SQL=" + sql);
		List l = jdbcTemplate.queryForList(sql);
		BigDecimal i = (BigDecimal) (((Map) l.get(0)).get("TABLESIZE"));
		return new Long(i.longValue());
	}

	public void delRowById(String sTableName, String idColName, String idValue) {
		String sql = "delete from {0} t where t.{1}= {2}";
		sql = genWholeSql(sql, new String[] { sTableName, idColName, idValue });
		log.info("SQL=" + sql);
		jdbcTemplate.update(sql);
	}

	public List getRowById(String sTableName, String idColName, String idValue) {
		String sql = "select * from {0} t where t.{1}= {2}";
		sql = genWholeSql(sql, new String[] { sTableName, idColName, idValue });
		log.info("SQL=" + sql);
		return jdbcTemplate.queryForList(sql);
	}

	public List getList(String sTableName, int istartPage, int pageSize, String sOrderByCol, String sOrderByType) {
		String sql = "SELECT * FROM\n" + "     (\n" + "     SELECT A.*, rownum r\n" + "     FROM\n" + "          (\n"
				+ "          select * from {0} t ORDER BY t.{1} {2}\n" + "          ) A\n"
				+ "     WHERE rownum <= {3}\n" + "     ) B\n" + "WHERE r > {4}";
		int irownumbegin = istartPage * pageSize;
		int irownumend = irownumbegin + pageSize;

		sql = genWholeSql(sql, new String[] { sTableName, sOrderByCol, sOrderByType,
				new Integer(irownumend).toString(), new Integer(irownumbegin).toString() });
		log.info("SQL=" + sql);
		return jdbcTemplate.queryForList(sql);
	}

	/*
	 * author zx 上面getList分页查询的扩展,可以跟入appendSql语句,方便需要条件的分页查询 example
	 * :　appendSql = "select * from T where ID = 'a' order by ID ASC"
	 */
	public List getList(int istartPage, int pageSize, String appendSql) {
		String sql = sPageSqlBefore + appendSql + sPageSqlAfter;
		int irownumbegin = istartPage * pageSize;
		int irownumend = irownumbegin + pageSize;

		return jdbcTemplate.queryForList(sql, new Object[] { new Integer(irownumend), new Integer(irownumbegin) });
	}

	public void updateRowById(String sTableName, String idColName, String idValue, Map params, Map vparamstypes) {
		String sql = "update {0} t set {1} where t.{2}= {3}";
		sql = genWholeSql(sql, new String[] { sTableName, getSetString(params, vparamstypes), idColName,
				idValue });
		log.info("SQL=" + sql);
		jdbcTemplate.update(sql);
	}

	public void addRow(String sTableName, String colNames, String values) {

		String sql = "insert into {0} ({1}) values ({2})";
		sql = genWholeSql(sql, new String[] { sTableName, colNames, values });
		log.info("SQL=" + sql);
		jdbcTemplate.update(sql);
	}

	public String getSetString(Map params, Map vparamstypes) {
		StringBuffer sb = new StringBuffer();
		for (Iterator iterator = params.keySet().iterator(); iterator.hasNext();) {
			String key = (String) iterator.next();
			String value = (String) params.get(key);
			sb.append("t.").append(key).append("=");
			if ("string".compareToIgnoreCase((String) vparamstypes.get(key)) == 0) {
				sb.append("'").append(value).append("',");
			} else {
				sb.append(value).append(",");
			}
		}
		return sb.deleteCharAt(sb.length() - 1).toString();
	}

	public String[] getColNamesAndValues(Map params, Map vparamstypes) {
		/*
		 * insert into PTSAM_T_AD_WORKFLOW (ID, FORMTYPE, FLOWTYPEID, FLOWTYPE,
		 * NODECODE, ISNEEDPASS, ISNEEDTRANSFER, SPECIALTY, LINKURL, REMARKS,
		 * BUSINESSURL) values (1, '1', 7, '验证通过', 'M', 1, 1, '盎司大是',
		 * '../../Business/AD/AD_JFlowValidate.aspx', '验证通过', null);
		 */
		StringBuffer sbColNames = new StringBuffer();
		StringBuffer sbValues = new StringBuffer();
		for (Iterator iterator = params.keySet().iterator(); iterator.hasNext();) {
			String key = (String) iterator.next();
			String value = (String) params.get(key);
			sbColNames.append(key).append(",");
			if ("string".compareToIgnoreCase((String) vparamstypes.get(key)) == 0) {
				sbValues.append("'").append(value).append("',");
			} else {
				sbValues.append(value).append(",");
			}
		}
		return new String[] { sbColNames.toString(), sbValues.toString() };
	}

	/*
	 * 执行传入的SQL查询语句
	 * 
	 * @author baiyf
	 */
	public List getListBySql(String sql) {
		log.info("SQL=" + sql);
		return jdbcTemplate.queryForList(sql);
	}

	public void updateBySql(String sql) {
		log.info("SQL=" + sql);
		// createlog(sql);
		jdbcTemplate.update(sql);
	}

	public void executeSql(String sql) {
		log.info("SQL=" + sql);
		// createlog(sql);
		jdbcTemplate.execute(sql);
	}

	/**
	 * 根据输入参数产生完整sql
	 * 
	 * @param sqlBody
	 * @param sOrderByCol
	 * @param sOrderByType
	 * @return
	 */
	protected String generateSql(String sqlBody, String strFilterSql, String sOrderByCol, String sOrderByType) {
		String orderBy = (sOrderByCol == null || sOrderByCol.equals("")) ? "ROWNUM" : sOrderByCol;
		String orderType = (sOrderByType == null || sOrderByType.equals("")) ? "ASC" : sOrderByType;
		if (strFilterSql != null && !strFilterSql.equals(""))
			sqlBody = sqlBody + " and " + strFilterSql;

		sqlBody = sqlBody + " ORDER BY " + orderBy + " " + orderType;
		return sqlBody;
	}

//	/**
//	 * 根据输入参数产生完整sql
//	 * 
//	 * @param sqlBody
//	 * @return
//	 */
//	private String generateSql(String sqlBody, String strFilterSql) {
//		if (strFilterSql != null && !strFilterSql.equals(""))
//			sqlBody = sqlBody + " and " + strFilterSql;
//		return sqlBody;
//	}

	private static String genWholeSql(String sin, final String[] args) {
		String s = sin;
		for (int i = 0; i < args.length; i++) {
			s = s.replaceAll("\\{" + i + "\\}", args[i]);
		}
		return s;
	}
}
